import pyodbc
import sys
import logging
from config import Config

logger = logging.getLogger(__name__)

def get_db():
    """创建数据库连接"""
    try:
        conn_str = (
            f"DRIVER={{{Config.DB_CONFIG['driver']}}};"
            f"SERVER={Config.DB_CONFIG['server']};"
            f"DATABASE={Config.DB_CONFIG['database']};"
            f"UID={Config.DB_CONFIG['user']};"
            f"PWD={Config.DB_CONFIG['password']};"
            f"TrustServerCertificate={Config.DB_CONFIG['trust_cert']};"
        )
        return pyodbc.connect(conn_str)
    except Exception as e:
        logger.error(f"数据库连接失败: {e}")
        return None

def init_database():
    """数据库初始化模块"""
    try:
        # 连接到master数据库
        master_conn = pyodbc.connect(
            f"DRIVER={{{Config.DB_CONFIG['driver']}}};"
            f"SERVER={Config.DB_CONFIG['server']};"
            f"UID={Config.DB_CONFIG['user']};"
            f"PWD={Config.DB_CONFIG['password']};"
            f"TrustServerCertificate={Config.DB_CONFIG['trust_cert']};",
            autocommit=True
        )

        try:
            with master_conn.cursor() as cursor:
                # 创建应用数据库
                cursor.execute(f"""
                    IF NOT EXISTS (
                        SELECT name FROM sys.databases 
                        WHERE name = '{Config.DB_CONFIG['database']}'
                    )
                    CREATE DATABASE {Config.DB_CONFIG['database']}
                """)

                # 切换到应用数据库
                cursor.execute(f"USE {Config.DB_CONFIG['database']}")

                # 创建数据表结构
                cursor.execute("""
                    IF NOT EXISTS (
                        SELECT * FROM sys.tables 
                        WHERE name = 'SensorData'
                    )
                    BEGIN
                        CREATE TABLE SensorData (
                            id INT IDENTITY(1,1) PRIMARY KEY,
                            temperature DECIMAL(5,2) NOT NULL 
                                CHECK (temperature BETWEEN 0 AND 50),
                            humidity DECIMAL(5,2) NOT NULL 
                                CHECK (humidity BETWEEN 20 AND 95),
                            record_time DATETIME2(0) NOT NULL 
                                DEFAULT SYSDATETIME(),
                            sensor_id VARCHAR(20) NOT NULL 
                                DEFAULT 'SENSOR-01'
                                CHECK (sensor_id LIKE 'SENSOR-[0-9][0-9]'),
                            is_valid AS CASE 
                                WHEN temperature BETWEEN 0 AND 50 
                                    AND humidity BETWEEN 20 AND 95 
                                THEN 1 ELSE 0 
                            END PERSISTED,
                            CONSTRAINT CHK_DataQuality CHECK (is_valid = 1)
                        )

                        CREATE INDEX idx_record_time 
                            ON SensorData (record_time DESC)
                            INCLUDE (temperature, humidity)
                    END
                """)

            logger.info("数据库初始化成功")

        finally:
            master_conn.close()

    except pyodbc.Error as e:
        logger.error(f"数据库初始化失败: {str(e)}")
        sys.exit(1)
    except Exception as e:
        logger.error(f"未知错误: {str(e)}")
        sys.exit(1)
